<?php
class ChidoansvsController extends AppController {

	var $name = 'Chidoansvs';
	var $uses = array('Chidoansv','Dantoc','Tongiao','Quanhuyen','Phuongxa','Tinhtp','Khoahoc','Tgiachidoan','Tgiachidoancb','Chucvucb','Chucvusv','User');
	function index($khoa_id) {
		$this->Chidoansv->recursive = 0;
		$this->set('chidoansvs', $this->paginate(array('khoa_id'=>$khoa_id)));
        $tenkhoa=$this->Chidoansv->query('select tenkhoa from khoas where id='.$khoa_id);
        $this->set(compact('khoa_id','tenkhoa'));
	}
    function search() {
        $this->layout='search';
        $column = array(
                        'Chidoansv.machidoansv',
                        'Chidoansv.tenchidoan'
                       );
        $value = $this->params['pass'];
        $khoa_id=$value[2];
        $this->set('khoa_id',$khoa_id);
        //$ii = 7;
        for($i=0;$i<count($column);$i++){
            if($value[$i]!="null"){
                $conditions[] = array($column[$i].' Like'=>'%'.$value[$i].'%');
            }
        }
        //pr($conditions);
        if(!empty($conditions)){
            $results=$this->Chidoansv->find('all',array('conditions'=>$conditions));
            $this->set('results', $results);    
        }//else $this->set('results', $this->paginate(array('chidoansv_id'=>$chidoan_id,'ngaychuyen'=>NULL)));
        $this->set('results', $results);          
    }
    function duyetcd($khoa_id) {
		$this->Chidoansv->recursive = 0;
		$this->set('chidoansvs', $this->paginate(array('khoa_id'=>$khoa_id)));
        $tenkhoa=$this->Chidoansv->query('select tenkhoa from khoas where id='.$khoa_id);
        $this->set(compact('khoa_id','tenkhoa'));
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid chidoansv', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('chidoansv', $this->Chidoansv->read(null, $id));
	}

	function add($khoa_id) {
		if (!empty($this->data))
		{
			$this->Chidoansv->create();
			if ($this->Chidoansv->save($this->data))
			{
				$this->Session->setFlash(__('Đã lưu', true),'success');
				$this->redirect(array('action' => 'index',$khoa_id));
			}
			else
			{
				$this->Session->setFlash(__('Lỗi khi lưu. vui long thử lại', true),'error');
			}
		}
		$nganhs = $this->Chidoansv->Nganh->find('list',array('fields'=>array('tennganh')));
		$khoahocs = $this->Chidoansv->Khoahoc->find('list',array('fields'=>array('nambatdau')));
		$khoas = $this->Chidoansv->Khoa->find('list');
		$this->set(compact('nganhs','khoahocs', 'khoas','khoa_id'));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid chidoansv', true));
			$this->redirect(array('action' => 'index',$this->data['Chidoansv'] ['khoa_id']));
		}
		if (!empty($this->data)) {
			if ($this->Chidoansv->save($this->data)) {
				$this->Session->setFlash(__('Thông tin đã lưu', true),'success');
				$this->redirect(array('action' => 'index',$this->data['Chidoansv'] ['khoa_id']));
			} else {
				$this->Session->setFlash(__('Thông tin không thể lưu', true),'error');
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Chidoansv->read(null, $id);
		}
		$nganhs = $this->Chidoansv->Nganh->find('list',array('fields'=>array('tennganh')));
		$khoahocs = $this->Chidoansv->Khoahoc->find('list',array('fields'=>array('nambatdau')));
		$khoas = $this->Chidoansv->Khoa->find('list');
		$this->set(compact('nganhs','khoahocs', 'khoas','khoa_id'));
	}

	function delete($id = null,$khoa_id=null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for chidoansv', true));
			$this->redirect(array('action'=>'index',$khoa_id));
		}
		if ($this->Chidoansv->delete($id)) {
			$this->Session->setFlash(__('Thông tin đã xóa', true),'success');
			$this->redirect(array('action'=>'index',$khoa_id));
		}
		$this->Session->setFlash(__('Thông tin không thể xóa', true),'error');
		$this->redirect(array('action' => 'index',$khoa_id));
	}

	function caychidoan($loaidv=null,$chidoansv_id=null)
	{
		$khoa_id= $this->Session->read('khoa_id');
		// can bo
			$querystringcb= "select Chidoansv.*
	    					from tgiachidoancbs as Tgiachidoancb,chidoansvs Chidoansv, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
	    					where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id 
	    							and Tgiachidoancb1.sl>0 
	    							and ngaychuyen is NULL
	    							and Chidoansv.id=Tgiachidoancb.chidoansv_id 
	    							and Chidoansv.khoa_id= ".$khoa_id."
	    						group by Tgiachidoancb.chidoansv_id";
			
			$laycd_cbs = $this->Chidoansv->query($querystringcb);
			
		//sinh vien
		$khoahocs = $this->Khoahoc->find('list',array('fields'=>array('nambatdau')));
		$querystringsv= "select Chidoansv.*
	    					from tgiachidoans as Tgiachidoan,chidoansvs Chidoansv, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
	    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id 
	    							and Tgiachidoan1.sl>0
	    							and ngaychuyen is NULL
	    							and Chidoansv.id=Tgiachidoan.chidoansv_id 
	    							and Chidoansv.khoa_id= ".$khoa_id."
	    						group by Tgiachidoan.chidoansv_id
	    						order by Chidoansv.khoahoc_id ASC";
		
		$laycd_svs = $this->Chidoansv->query($querystringsv);
	    
	    //cau truc lai data
	    $chidoans = array();
	    $chidoans[0]['id']='chidoan_0';
	    $chidoans[0]['tencd']='Chi đoàn';
	    $chidoans[0]['loaidv']='cd';
	    $chidoans[0]['id_cha']=-3;
	    
	    $chidoans[1]['id']='chidoan_2';
	    $chidoans[1]['tencd']='Chi đoàn Cán Bộ';
	    $chidoans[1]['loaidv']='cbl';
	    $chidoans[1]['id_cha']='chidoan_0';
	    
	    $chidoans[2]['id']='chidoan_1';
	    $chidoans[2]['tencd']='Chi đoàn Sinh Viên';
	    $chidoans[2]['loaidv']='svl';
	    $chidoans[2]['id_cha']='chidoan_0';
	    foreach ($laycd_cbs as $chdoancb)
	    {
	    	$count = count($chidoans);
	    	$chidoans[$count]['id']=$chdoancb['Chidoansv']['id'];
	    	$chidoans[$count]['tencd']=$chdoancb['Chidoansv']['machidoansv'].'-'.$chdoancb['Chidoansv']['tenchidoan'];
	    	$chidoans[$count]['loaidv']='cb';
	    	$chidoans[$count]['id_cha']='chidoan_2';	    		    	
	    }
	    
		$khhoccos =array();
	    foreach ($laycd_svs as $chdoansv) 
	    {
	    	$khhoccos[$chdoansv['Chidoansv']['khoahoc_id']]=$chdoansv['Chidoansv']['khoahoc_id'];	    	
	    }
	   foreach ($khhoccos as $khhoc_id)
	    {
	    	$count = count($chidoans);
	    	$chidoans[$count]['id']='khhoc_'.$khhoc_id;
	    	$chidoans[$count]['tencd']=$khoahocs[$khhoc_id];
	    	$chidoans[$count]['loaidv']='kh_hoc';
	    	$chidoans[$count]['id_cha']='chidoan_1';
	    }
		foreach ($laycd_svs as $chdoansv) 
	    {
	    	$count = count($chidoans);
	    	$chidoans[$count]['id']=$chdoansv['Chidoansv']['id'];
	    	$chidoans[$count]['tencd']=$chdoansv['Chidoansv']['machidoansv'].'-'.$chdoansv['Chidoansv']['tenchidoan'];
	    	$chidoans[$count]['loaidv']='sv';
	    	$chidoans[$count]['id_cha']='khhoc_'.$chdoansv['Chidoansv']['khoahoc_id'];
	    }
	    
	    	$thongkedoanvien=array();
	    	$thongkethanhnien =array();
	    	$thongkenamnu =array();
	    	$thongkedangvien =array();
	    if($loaidv && $chidoansv_id)
	    {
		    if($loaidv=='sv')
			{
				$dv = $this->Tgiachidoan->find('list',array('conditions'=>array('chidoansv_id'=>$chidoansv_id),'fields'=>array('sinhvien_id')));
				$this->Chidoansv->unbindModel(array('belongsTo'=>array('Khoa','Khoahoc'),'hasMany'=>array('Loaicdcb','Loaicdsv','Phicdcb','Phicdsv','Phongtraocb','Phongtraosv','Tgiachidoan','Tgiachidoancb','Tgdongdoanphicb','Tgdongdoanphisv')),false);
				$this->Tgiachidoan->Sinhvien->unbindModel(array('hasMany'=>array('Chucvusv','Dvutsv','Phanloaisv','Qdketnap','Quyetdinhttdoansv','Tgdongdoanphisv','Tgiachidoan','Thanhtichkhoasv','Thanhtichsv')),false);
				$this->paginate['Tgiachidoan']=array('conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id),'recursive'=>2);
				$thongkenamnu = $this->Tgiachidoan->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id),'fields'=>array('Chidoansv.*','Sinhvien.phai','count(Sinhvien.phai) as soluong'),'group'=>array('Sinhvien.phai')));
				$thongkedangvien = $this->Tgiachidoan->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('Sinhvien.ngayvaodang !='=>null),array('Sinhvien.ngayvaodang !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong')));
				$joins =array(array('table' => 'sinhviens',
            						'type' => 'INNER',
            						'alias' => 'Sinhvien',
            						'conditions' => array(
                					'Tgiachidoan.sinhvien_id = Sinhvien.id')),
									array('table' => 'chidoansvs',
            						'type' => 'INNER',
            						'alias' => 'Chidoansv',
            						'conditions' => array(
                					'Tgiachidoan.chidoansv_id = Chidoansv.id')));
				$thongkedoanvien = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),array('Sinhvien.ngayvaodang'=>'0000-00-00')),'AND'=>array(array('Sinhvien.ngayvaodoan !='=>null),array('Sinhvien.ngayvaodoan !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'))));
				$thongkethanhnien = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),array('Sinhvien.ngayvaodang'=>'0000-00-00')),'OR'=>array(array('Sinhvien.ngayvaodoan '=>null),array('Sinhvien.ngayvaodoan'=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'))));
				//$thongkettdoan = $this->Tgiachidoan->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('Sinhvien.truongthanhdoan !='=>null),array('Sinhvien.truongthanhdoan !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong')));
				$doanviens = $this->paginate('Tgiachidoan');
				
				$dviens = array();
			foreach ($doanviens as $dvien)
			{
				$listdvs[]=$dvien['Sinhvien']['mssv'];
			}
			$coacc = $this->User->find('list',array('conditions'=>array('username'=>$listdvs),'fields'=>array('username')));
				
				$chucvus = $this->Chucvusv->find('list',array('recursive'=>0,'conditions'=>array('sinhvien_id'=>$dv),'fields'=>array('sinhvien_id','Chucv.tenchucvu')));
				$tenloaidv ='Sinhvien';
				$maso ='mssv';
				$ttdoan='truongthanhdoan';
			}
			elseif($loaidv=='cb')
			{
				$dv = $this->Tgiachidoancb->find('list',array('conditions'=>array('chidoansv_id'=>$chidoansv_id),'fields'=>array('canbo_id')));
				$this->Chidoansv->unbindModel(array('belongsTo'=>array('Khoa','Khoahoc'),'hasMany'=>array('Loaicdcb','Loaicdsv','Phicdcb','Phicdsv','Phongtraocb','Phongtraosv','Tgiachidoan','Tgiachidoancb','Tgdongdoanphicb','Tgdongdoanphisv')),false);
				$this->Tgiachidoancb->Canbo->unbindModel(array('hasMany'=>array('Chucvucb','Dvutcb','Phanloaicb','Quyetdinhttdoancb','Tgdongdoanphicb','Tgiachidoancb','Thanhtichkhoacb','Thanhtichcb')),false);
				$this->paginate['Tgiachidoancb']=array('conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id),'recursive'=>2);
				$thongkenamnu = $this->Tgiachidoancb->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id),'fields'=>array('Chidoansv.*','Canbo.phai','count(Canbo.phai) as soluong'),'group'=>array('Canbo.phai')));
				$thongkedangvien = $this->Tgiachidoancb->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('Canbo.ngayvaodang !='=>null),array('Canbo.ngayvaodang !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong')));
				$joins =array(array('table' => 'canbos',
            						'type' => 'INNER',
            						'alias' => 'Canbo',
            						'conditions' => array(
                					'Tgiachidoancb.canbo_id = Canbo.id')),
									array('table' => 'chidoansvs',
            						'type' => 'INNER',
            						'alias' => 'Chidoansv',
            						'conditions' => array(
                					'Tgiachidoancb.chidoansv_id = Chidoansv.id')));
				$thongkedoanvien = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'OR'=>array(array('Canbo.ngayvaodang'=>NULL),array('Canbo.ngayvaodang'=>'0000-00-00')),'AND'=>array(array('Canbo.ngayvaodoan !='=>null),array('Canbo.ngayvaodoan !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'))));
				$thongkethanhnien = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'OR'=>array(array('Canbo.ngayvaodang'=>NULL),array('Canbo.ngayvaodang'=>'0000-00-00')),'OR'=>array(array('Canbo.ngayvaodoan '=>null),array('Canbo.ngayvaodoan'=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'))));
				//$thongkettdoan = $this->Tgiachidoancb->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('Sinhvien.truongthanhdoan !='=>null),array('Sinhvien.truongthanhdoan !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong')));
				$doanviens = $this->paginate('Tgiachidoancb');
				
				$dviens = array();
			foreach ($doanviens as $dvien)
			{
				$listdvs[]=$dvien['Canbo']['mscb'];
			}
			$coacc = $this->User->find('list',array('conditions'=>array('username'=>$listdvs),'fields'=>array('username')));
				
				$chucvus = $this->Chucvucb->find('list',array('recursive'=>0,'conditions'=>array('canbo_id'=>$dv),'fields'=>array('canbo_id','Chucv.tenchucvu')));
				$tenloaidv ='Canbo';
				$maso ='mscb';
				$ttdoan = 'ngayttdoan';
			}
	    }
	    $quanhuyens  = $this->Quanhuyen->find('all');
	    $this->set(compact('quanhuyens','coacc','maso','tenloaidv','chucvus','doanviens','chidoansv_id','loaidv','ttdoan','thongkenamnu','thongkedangvien','thongkedoanvien','thongkethanhnien','thongkettdoan'));
    	$this->set(compact('chidoans'));
	}
	
	function doanviencd($loaidv='',$chidoansv_id=0)
	{
		$this->layout='ajax';
		if($loaidv=='sv')
		{
			$dv = $this->Tgiachidoan->find('list',array('conditions'=>array('chidoansv_id'=>$chidoansv_id),'fields'=>array('sinhvien_id')));
			$this->Chidoansv->unbindModel(array('belongsTo'=>array('Khoa','Khoahoc'),'hasMany'=>array('Loaicdcb','Loaicdsv','Phicdcb','Phicdsv','Phongtraocb','Phongtraosv','Tgiachidoan','Tgiachidoancb','Tgdongdoanphicb','Tgdongdoanphisv')),false);
			$this->Tgiachidoan->Sinhvien->unbindModel(array('hasMany'=>array('Chucvusv','Dvutsv','Phanloaisv','Qdketnap','Quyetdinhttdoansv','Tgdongdoanphisv','Tgiachidoan','Thanhtichkhoasv','Thanhtichsv')),false);
			$this->paginate['Tgiachidoan']=array('conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id),'recursive'=>2);
			$doanviens = $this->paginate('Tgiachidoan');
			
			$dviens = array();
			foreach ($doanviens as $dvien)
			{
				$listdvs[]=$dvien['Sinhvien']['mssv'];
			}
			$coacc = $this->User->find('list',array('conditions'=>array('username'=>$listdvs),'fields'=>array('username')));
			
			$thongkenamnu = $this->Tgiachidoan->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id),'fields'=>array('Chidoansv.*','Sinhvien.phai','count(Sinhvien.phai) as soluong'),'group'=>array('Sinhvien.phai')));
			$thongkedangvien = $this->Tgiachidoan->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('Sinhvien.ngayvaodang !='=>null),array('Sinhvien.ngayvaodang !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong')));
			$joins =array(array('table' => 'sinhviens',
            						'type' => 'INNER',
            						'alias' => 'Sinhvien',
            						'conditions' => array(
                					'Tgiachidoan.sinhvien_id = Sinhvien.id')),
									array('table' => 'chidoansvs',
            						'type' => 'INNER',
            						'alias' => 'Chidoansv',
            						'conditions' => array(
                					'Tgiachidoan.chidoansv_id = Chidoansv.id')));
			$thongkedoanvien = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),array('Sinhvien.ngayvaodang'=>'0000-00-00')),'AND'=>array(array('Sinhvien.ngayvaodoan !='=>null),array('Sinhvien.ngayvaodoan !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'))));
			$thongkethanhnien = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),array('Sinhvien.ngayvaodang'=>'0000-00-00')),'OR'=>array(array('Sinhvien.ngayvaodoan '=>null),array('Sinhvien.ngayvaodoan'=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'))));
			//$thongkettdoan = $this->Tgiachidoan->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('Sinhvien.truongthanhdoan !='=>null),array('Sinhvien.truongthanhdoan !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong')));
			$chucvus = $this->Chucvusv->find('list',array('recursive'=>0,'conditions'=>array('sinhvien_id'=>$dv),'fields'=>array('sinhvien_id','Chucv.tenchucvu')));
			$tenloaidv ='Sinhvien';
			$maso ='mssv';
			$ttdoan='truongthanhdoan';
		}
		elseif($loaidv=='cb')
		{
			$dv = $this->Tgiachidoancb->find('list',array('conditions'=>array('chidoansv_id'=>$chidoansv_id),'fields'=>array('canbo_id')));
			$this->Chidoansv->unbindModel(array('belongsTo'=>array('Khoa','Khoahoc'),'hasMany'=>array('Loaicdcb','Loaicdsv','Phicdcb','Phicdsv','Phongtraocb','Phongtraosv','Tgiachidoan','Tgiachidoancb','Tgdongdoanphicb','Tgdongdoanphisv')),false);
			$this->Tgiachidoancb->Canbo->unbindModel(array('hasMany'=>array('Chucvucb','Dvutcb','Phanloaicb','Quyetdinhttdoancb','Tgdongdoanphicb','Tgiachidoancb','Thanhtichkhoacb','Thanhtichcb')),false);
			$this->paginate['Tgiachidoancb']=array('conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id),'recursive'=>2);
			$thongkenamnu = $this->Tgiachidoancb->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id),'fields'=>array('Chidoansv.*','Canbo.phai','count(Canbo.phai) as soluong'),'group'=>array('Canbo.phai')));
			$thongkedangvien = $this->Tgiachidoancb->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('Canbo.ngayvaodang !='=>null),array('Canbo.ngayvaodang !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong')));
			$joins =array(array('table' => 'canbos',
            						'type' => 'INNER',
            						'alias' => 'Canbo',
            						'conditions' => array(
                					'Tgiachidoancb.canbo_id = Canbo.id')),
									array('table' => 'chidoansvs',
            						'type' => 'INNER',
            						'alias' => 'Chidoansv',
            						'conditions' => array(
                					'Tgiachidoancb.chidoansv_id = Chidoansv.id')));
			$thongkedoanvien = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'OR'=>array(array('Canbo.ngayvaodang'=>NULL),array('Canbo.ngayvaodang'=>'0000-00-00')),'AND'=>array(array('Canbo.ngayvaodoan !='=>null),array('Canbo.ngayvaodoan !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'))));
			$thongkethanhnien = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('OR'=>array(array('Canbo.ngayvaodang'=>NULL),array('Canbo.ngayvaodang'=>'0000-00-00'))),array('OR'=>array(array('Canbo.ngayvaodoan '=>null),array('Canbo.ngayvaodoan'=>'0000-00-00'))))),'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'))));
			//$thongkettdoan = $this->Tgiachidoancb->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,'chidoansv_id'=>$chidoansv_id,'AND'=>array(array('Sinhvien.truongthanhdoan !='=>null),array('Sinhvien.truongthanhdoan !='=>'0000-00-00'))),'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong')));
			$doanviens = $this->paginate('Tgiachidoancb');
			$dviens = array();
			foreach ($doanviens as $dvien)
			{
				$listdvs[]=$dvien['Canbo']['mscb'];
			}
			$coacc = $this->User->find('list',array('conditions'=>array('username'=>$listdvs),'fields'=>array('username')));
			$chucvus = $this->Chucvucb->find('list',array('recursive'=>0,'conditions'=>array('canbo_id'=>$dv),'fields'=>array('canbo_id','Chucv.tenchucvu')));
			$tenloaidv ='Canbo';
			$maso ='mscb';
			$ttdoan = 'ngayttdoan';
		}
		$quanhuyens  = $this->Quanhuyen->find('all');
		$this->set(compact('quanhuyens','coacc','maso','tenloaidv','chucvus','doanviens','chidoansv_id','loaidv','ttdoan','thongkenamnu','thongkedangvien','thongkedoanvien','thongkethanhnien','thongkettdoan'));
	}

	function thongkedv($namess = null)
	{	
		$khoa_id = $this->Session->read('khoa_id');
        $tenkhoa= $this->Chidoansv->query("select tenkhoa from khoas where id=".$khoa_id);
		$dantocs = $this->Dantoc->find('list',array('fields'=>array('tendantoc')));
		$tongiaos = $this->Tongiao->find('list',array('fields'=>array('tentongiao')));
		$khoahocs = $this->Khoahoc->find('list',array('fields'=>array('nambatdau')));
		$tinhtps = $this->Tinhtp->find('list',array('fields'=>array('tentinhtp')));
		
		//sinh vien
		$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
    						group by Tgiachidoan.chidoansv_id";
    	$laycdsv_act = $this->Chidoansv->query($querystring);
    	
    	$cdsv_id = array();
    	foreach ($laycdsv_act as $cd) 
    	{
    		$cdsv_id[] = $cd['Tgiachidoan']['chidoansv_id'];
    	}    	
		//hien thi sinhvien can cu vao $sv 
		$this->Chidoansv->displayField='chidoan' ;
    	$cdsvs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id !='=>null,'id'=>$cdsv_id)));
    	$chidoansvs =array();
    	foreach ($cdsvs as $key => $value)
    	{
    		$chidoansvs[$key.'_sv']=$value;
    	}
    	
    	/// can bo
    	$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
    					where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
    						group by Tgiachidoancb.chidoansv_id";
    	$laycdcb_act = $this->Chidoansv->query($querystring);
    	
    	$cdcb_id = array();
    	foreach ($laycdcb_act as $cd) 
    	{
    		$cdcb_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
    	}    
		$this->Chidoansv->displayField='chidoan' ;
    	$cdcbs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>null,'id'=>$cdcb_id)));
		foreach ($cdcbs as $key => $value)
    	{
    		$chidoancbs[$key.'_cb']=$value;
    	}
    	//chi doan
    	if(empty($chidoancbs))
    		$chidoancbs =array();
    	$chidoans = array('Cán bộ'=>$chidoancbs,'Sinh viên'=>$chidoansvs);
    	
		if($namess != null)
	    {
	    	$this->data=$this->Session->read($namess);
	    }
	    else
	    { 
	    	if(!empty($this->data))
	    	{
		    	$name = 'datatk'.date('Ymdhis');
				$this->Session->write($name,$this->data);
		    	$this->redirect(array('action'=>'thongkedv',$name));
	    	}
	    }
		if(!empty($this->data))
		{
			
			$tgiao = array();
			$dtoc = array();
			$tinhtp =array();
			if(!empty($this->data['Chidoansv']['tongiao_id']))
			{
				$tgiao =array('tongiao_id'=>$this->data['Chidoansv']['tongiao_id']);
			}
			if(!empty($this->data['Chidoansv']['dantoc_id']))
			{
				$dtoc = array('dantoc_id'=>$this->data['Chidoansv']['dantoc_id']);
			}
			if(!empty($this->data['Chidoansv']['tinhtp_id']))
			{
				$ttp = $this->Phuongxa->find('list',array('recursive'=>'1','conditions'=>array('Quanhuyen.tinhtp_id'=>$this->data['Chidoansv']['tinhtp_id']),'fields'=>array('Phuongxa.id','Phuongxa.id')));
				if(!empty($ttp))
				{
					$tinhtp=array('phuongxa_id'=>$ttp);
				}
			}
			
			$cdcb =array();
			$cdsv=array();
			if(!empty($this->data['Chidoansv']['chidoansv_id']))
			{
				foreach ($this->data['Chidoansv']['chidoansv_id'] as $value)
				{
					$tmp = split('_',$value);
					if($tmp[1]=='cb')
						$cdcb[]=$tmp[0];
					else
						$cdsv[]=$tmp[0];
				}
				if(empty($cdsv))
				{
					$cdoansvtk=array();
					//$cdoansv =array('Chidoansv.khoa_id'=>$khoa_id);
					$cdoansv =array('Chidoansv.id'=>array());
				}
				else
				{
					$this->Chidoansv->displayField='chidoan' ;
					$cdoansvtk = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'id'=>$cdsv)));				
					$cdoansv =array('Chidoansv.id'=>$cdsv);
				}
				// can bo
				if(empty($cdcb))
				{
					$cdoancbtk =array();
					$cdoancb =array('Chidoansv.id'=>array());
				}
				else
				{
					$this->Chidoansv->displayField='chidoan' ;
					$cdoancbtk = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'id'=>$cdcb)));
					$cdoancb =array('Chidoansv.id'=>$cdcb);
				}
			}
			else
			{
				if($this->data['Chidoansv']['loaidv']=='cb')
				{
					$cdoansvtk=array();
					$cdoansv =array('Chidoansv.id'=>array());
					
					$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
	    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
    						where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
    							group by Tgiachidoancb.chidoansv_id";
    					$laycdcb_act = $this->Chidoansv->query($querystring);
    	
    					$cdcb_id = array();
    					foreach ($laycdcb_act as $cd) 
				    	{
				    		$cdcb_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
				    	}    
						$this->Chidoansv->displayField='chidoan' ;
				    	$cdcbs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'id'=>$cdcb_id)));
					$cdoancbtk =$cdcbs;
					$cdoancb =array('Chidoansv.id'=>array_keys($cdcbs));
				}
				elseif($this->data['Chidoansv']['loaidv']=='sv')
				{
						//sinh vien
						$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
				    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
				    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
				    						group by Tgiachidoan.chidoansv_id";
				    	$laycdsv_act = $this->Chidoansv->query($querystring);
				    	
				    	$cdsv_id = array();
				    	foreach ($laycdsv_act as $cd) 
				    	{
				    		$cdsv_id[] = $cd['Tgiachidoan']['chidoansv_id'];
				    	}
				    	if(empty($this->data['Chidoansv']['khoahocid']))
				    		$kh_id = array_keys($khoahocs);
				    	else
				    		$kh_id = $this->data['Chidoansv']['khoahocid'];
						//hien thi sinhvien can cu vao $sv 
						$this->Chidoansv->displayField='chidoan' ;
				    	$cdsv = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>$kh_id,'id'=>$cdsv_id)));
						$cdoancbtk =array();
						$cdoancb =array('Chidoansv.id'=>array());
						$cdoansvtk = $cdsv;
						$cdoansv =array('Chidoansv.id'=>array_keys($cdsv));
				}
				else
				{
					$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
			    						group by Tgiachidoan.chidoansv_id";
			    	$laycdsv_act = $this->Chidoansv->query($querystring);
			    	
			    	$cdsv_id = array();
			    	foreach ($laycdsv_act as $cd) 
			    	{
			    		$cdsv_id[] = $cd['Tgiachidoan']['chidoansv_id'];
			    	}    	
					//hien thi sinhvien can cu vao $sv 
					$this->Chidoansv->displayField='chidoan' ;
			    	$cdsvs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id !='=>null,'id'=>$cdsv_id)));
			    	
			    	/// can bo
			    	$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
			    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
			    					where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
			    						group by Tgiachidoancb.chidoansv_id";
			    	$laycdcb_act = $this->Chidoansv->query($querystring);
			    	
			    	$cdcb_id = array();
			    	foreach ($laycdcb_act as $cd) 
			    	{
			    		$cdcb_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
			    	}    
					$this->Chidoansv->displayField='chidoan' ;
			    	$cdcbs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>null,'id'=>$cdcb_id)));
			    	
			    	$cdoancbtk =$cdcbs;
					$cdoancb =array('Chidoansv.id'=>array_keys($cdcbs));
					$cdoansvtk = $cdsvs;
					$cdoansv =array('Chidoansv.id'=>array_keys($cdsvs));
				}
			}
			//sinh vien
			$joins =array(array('table' => 'sinhviens',
            						'type' => 'INNER',
            						'alias' => 'Sinhvien',
            						'conditions' => array(
                					'Tgiachidoan.sinhvien_id = Sinhvien.id')),
									array('table' => 'chidoansvs',
            						'type' => 'INNER',
            						'alias' => 'Chidoansv',
            						'conditions' => array(
                					'Tgiachidoan.chidoansv_id = Chidoansv.id')));
			$thongkethanhniensv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'OR'=>array(array('Sinhvien.ngayvaodoan '=>null),
																																				array('Sinhvien.ngayvaodoan'=>'0000-00-00'))),
																												'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'),
																												'group'=>array('Chidoansv.id'))));
			$thongkethanhniennamnusv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'Sinhvien.phai'=>'Nữ',
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'OR'=>array(array('Sinhvien.ngayvaodoan '=>null),
																																				array('Sinhvien.ngayvaodoan'=>'0000-00-00'))),
																												'fields'=>array('Chidoansv.*','Sinhvien.phai','count(Sinhvien.id) as soluong'),
																												'group'=>array('Chidoansv.id','Sinhvien.phai'))));
			$thongkedoanviensv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'AND'=>array(array('Sinhvien.ngayvaodoan !='=>null),
																																				array('Sinhvien.ngayvaodoan !='=>'0000-00-00'))),
																												'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'),
																												'group'=>array('Chidoansv.id'))));
			$thongkedoanviennamnusv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'Sinhvien.phai'=>'Nữ',
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'AND'=>array(array('Sinhvien.ngayvaodoan !='=>null),
																																				array('Sinhvien.ngayvaodoan !='=>'0000-00-00'))),
																												'fields'=>array('Chidoansv.*','Sinhvien.phai','count(Sinhvien.phai) as soluong'),
																												'group'=>array('Chidoansv.id','Sinhvien.phai'))));
			$thongkedangviensv = $this->Tgiachidoan->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,
																										$cdoansv,$dtoc,$tgiao,$tinhtp,
																										'AND'=>array(array('Sinhvien.ngayvaodang !='=>null),
																													array('Sinhvien.ngayvaodang !='=>'0000-00-00'))),
																						'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'),
																						'group'=>array('Chidoansv.id')));
			$tsthanhniensv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'OR'=>array(array('Sinhvien.ngayvaodoan '=>null),
																																				array('Sinhvien.ngayvaodoan'=>'0000-00-00'))),
																												'fields'=>array('count(Sinhvien.id) as soluong'))));
			$tsdoanviensv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'AND'=>array(array('Sinhvien.ngayvaodoan !='=>null),
																																				array('Sinhvien.ngayvaodoan !='=>'0000-00-00'))),
																												'fields'=>array('count(Sinhvien.id) as soluong'))));
			//can bo
		$joins =array(array('table' => 'canbos',
            						'type' => 'INNER',
            						'alias' => 'Canbo',
            						'conditions' => array(
                					'Tgiachidoancb.canbo_id = Canbo.id')),
									array('table' => 'chidoansvs',
            						'type' => 'INNER',
            						'alias' => 'Chidoansv',
            						'conditions' => array(
                					'Tgiachidoancb.chidoansv_id = Chidoansv.id')));
		
		$thongkedoanviencb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'AND'=>array(array('Canbo.ngayvaodoan !='=>null),
																																					array('Canbo.ngayvaodoan !='=>'0000-00-00'))),
																													'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'),
																													'group'=>array('Chidoansv.id'))));
		$thongkedoanviennamnucb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'Canbo.phai'=>'Nữ',
																																		$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'AND'=>array(array('Canbo.ngayvaodoan !='=>null),
																																					array('Canbo.ngayvaodoan !='=>'0000-00-00'))),
																													'fields'=>array('Chidoansv.*','Canbo.phai','count(Canbo.phai) as soluong'),
																													'group'=>array('Chidoansv.id','Canbo.phai'))));
		$thongkethanhniencb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'OR'=>array(array('Canbo.ngayvaodoan '=>null),
																																					array('Canbo.ngayvaodoan'=>'0000-00-00'))),
																													'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'),
																													'group'=>array('Chidoansv.id'))));
		$thongkethanhniennamnucb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'Canbo.phai'=>'Nữ',
																																		$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'OR'=>array(array('Canbo.ngayvaodoan '=>null),
																																					array('Canbo.ngayvaodoan'=>'0000-00-00'))),
																													'fields'=>array('Chidoansv.*','Canbo.phai','count(Canbo.phai) as soluong'),
																													'group'=>array('Chidoansv.id','Canbo.phai'))));
		$thongkedangviencb = $this->Tgiachidoancb->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,
																										$cdoancb,$dtoc,$tgiao,$tinhtp,
																										'AND'=>array(array('Canbo.ngayvaodang !='=>null),
																														array('Canbo.ngayvaodang !='=>'0000-00-00'))),
																					'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'),
																					'group'=>array('Chidoansv.id')));
																														
		$tsthanhniencb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'OR'=>array(array('Canbo.ngayvaodoan '=>null),
																																					array('Canbo.ngayvaodoan'=>'0000-00-00'))),
																													'fields'=>array('count(Canbo.id) as soluong'))));
		$tsdoanviencb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'AND'=>array(array('Canbo.ngayvaodoan !='=>null),
																																					array('Canbo.ngayvaodoan !='=>'0000-00-00'))),
																													'fields'=>array('count(Canbo.id) as soluong'))));
		
																																					
		
		$thongke['thanhnien']=(isset($tsthanhniensv[0][0]['soluong'])?$tsthanhniensv[0][0]['soluong']:0)+(isset($tsthanhniencb[0][0]['soluong'])?$tsthanhniencb[0][0]['soluong']:0);
		$thongke['doanvien']=(isset($tsdoanviensv[0][0]['soluong'])?$tsdoanviensv[0][0]['soluong']:0)+(isset($tsdoanviencb[0][0]['soluong'])?$tsdoanviencb[0][0]['soluong']:0);
		
		$this->set(compact('thongke','namess'));
		$this->set(compact('cdoansvtk','thongkethanhniensv','thongkethanhniennamnusv','thongkedoanviensv','thongkedoanviennamnusv','thongkedangviensv'));		
		$this->set(compact('cdoancbtk','thongkethanhniencb','thongkethanhniennamnucb','thongkedoanviencb','thongkedoanviennamnucb','thongkedangviencb'));
		}
		$this->set(compact('tongiaos','dantocs','khoahocs','tinhtps','chidoans','tenkhoa'));
	}

	function updatechidoan()
	{
		$this->layout='ajax';
		$khoahocs = $this->data['Chidoansv']['khoahocid'];
		if(!empty($khoahocs))
		{
			$kh = array('khoahoc_id'=>$khoahocs);
		}
		else
		{
			$khoahocs = $this->Khoahoc->find('list',array('fields'));
			$kh = array('khoahoc_id'=>$khoahocs);
		}
		
		$khoa_id = $this->Session->read('khoa_id');
		if($this->data['Chidoansv']['loaidv']=='')
		{
			//sinh vien
			$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
	    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
	    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
	    						group by Tgiachidoan.chidoansv_id";
	    	$laycdsv_act = $this->Chidoansv->query($querystring);
	    	
	    	$cdsv_id = array();
	    	foreach ($laycdsv_act as $cd) 
	    	{
	    		$cdsv_id[] = $cd['Tgiachidoan']['chidoansv_id'];
	    	}    
			//hien thi sinhvien can cu vao $sv 
			$this->Chidoansv->displayField='chidoan' ;
	    	$cdsvs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,$kh,'id'=>$cdsv_id)));
	    	$chidoansvs=array();
			foreach ($cdsvs as $key => $value)
	    	{
	    		$chidoansvs[$key.'_sv']=$value;
	    	}
	    	/// can bo
	    	$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
	    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
	    					where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
	    						group by Tgiachidoancb.chidoansv_id";
	    	$laycdcb_act = $this->Chidoansv->query($querystring);
	    	
	    	$cdcb_id = array();
	    	foreach ($laycdcb_act as $cd) 
	    	{
	    		$cdcb_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
	    	}    
			$this->Chidoansv->displayField='chidoan' ;
	    	$cdcbs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>null,'id'=>$cdcb_id)));
			foreach ($cdcbs as $key => $value)
	    	{
	    		$chidoancbs[$key.'_cb']=$value;
	    	}
	    	//chi doan
	    	$chidoans = array('Cán bộ'=>$chidoancbs,'Sinh viên'=>$chidoansvs);
		}
		elseif($this->data['Chidoansv']['loaidv']=='cb')
		{
			/// can bo
	    	$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
	    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
	    					where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
	    						group by Tgiachidoancb.chidoansv_id";
	    	$laycdcb_act = $this->Chidoansv->query($querystring);
	    	
	    	$cdcb_id = array();
	    	foreach ($laycdcb_act as $cd) 
	    	{
	    		$cdcb_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
	    	}    
			$this->Chidoansv->displayField='chidoan' ;
	    	$cdcbs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>null,'id'=>$cdcb_id)));
			foreach ($cdcbs as $key => $value)
	    	{
	    		$chidoancbs[$key.'_cb']=$value;
	    	}
	    	$chidoans = array('Cán bộ'=>$chidoancbs);
		}
		else
		{
			$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
	    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
	    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
	    						group by Tgiachidoan.chidoansv_id";
	    	$laycdsv_act = $this->Chidoansv->query($querystring);
	    	
	    	$cdsv_id = array();
	    	foreach ($laycdsv_act as $cd) 
	    	{
	    		$cdsv_id[] = $cd['Tgiachidoan']['chidoansv_id'];
	    	}    
	    	$khoa_id = $this->Session->read('khoa_id');
			//hien thi sinhvien can cu vao $sv 
			$this->Chidoansv->displayField='chidoan' ;
	    	$cdsvs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,$kh,'id'=>$cdsv_id)));
			foreach ($cdsvs as $key => $value)
	    	{
	    		$chidoansvs[$key.'_sv']=$value;
	    	}
	    	$chidoans = array('Sinh viên'=>$chidoansvs);
		}
		$this->set(compact('chidoans'));
	}
	
	function xuatexcel($namess=null)
	{
		if(!$namess)
			$this->redirect(array('action'=>'thongkedv'));
		$this->layout ='';
		
		$khoahocs = $this->Khoahoc->find('list',array('fields'=>array('nambatdau')));
		$khoa_id = $this->Session->read('khoa_id');
		$this->data=$this->Session->read($namess);	    
		
        $tenkhoa= $this->Chidoansv->query("select tenkhoa from khoas where id=".$khoa_id);
		if(!empty($this->data))
		{
			
			$tgiao = array();
			$dtoc = array();
			$tinhtp =array();
			if(!empty($this->data['Chidoansv']['tongiao_id']))
			{
				$tgiao =array('tongiao_id'=>$this->data['Chidoansv']['tongiao_id']);
			}
			if(!empty($this->data['Chidoansv']['dantoc_id']))
			{
				$dtoc = array('dantoc_id'=>$this->data['Chidoansv']['dantoc_id']);
			}
			if(!empty($this->data['Chidoansv']['tinhtp_id']))
			{
				$ttp = $this->Phuongxa->find('list',array('recursive'=>'1','conditions'=>array('Quanhuyen.tinhtp_id'=>$this->data['Chidoansv']['tinhtp_id']),'fields'=>array('Phuongxa.id','Phuongxa.id')));
				if(!empty($ttp))
				{
					$tinhtp=array('phuongxa_id'=>$ttp);
				}
			}
			
			$cdcb =array();
			$cdsv=array();
			if(!empty($this->data['Chidoansv']['chidoansv_id']))
			{
				foreach ($this->data['Chidoansv']['chidoansv_id'] as $value)
				{
					$tmp = split('_',$value);
					if($tmp[1]=='cb')
						$cdcb[]=$tmp[0];
					else
						$cdsv[]=$tmp[0];
				}
				if(empty($cdsv))
				{
					$cdoansvtk=array();
					//$cdoansv =array('Chidoansv.khoa_id'=>$khoa_id);
					$cdoansv =array('Chidoansv.id'=>array());
				}
				else
				{
					$this->Chidoansv->displayField='chidoan' ;
					$cdoansvtk = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'id'=>$cdsv)));				
					$cdoansv =array('Chidoansv.id'=>$cdsv);
				}
				// can bo
				if(empty($cdcb))
				{
					$cdoancbtk =array();
					$cdoancb =array('Chidoansv.id'=>array());
				}
				else
				{
					$this->Chidoansv->displayField='chidoan' ;
					$cdoancbtk = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'id'=>$cdcb)));
					$cdoancb =array('Chidoansv.id'=>$cdcb);
				}
			}
			else
			{
				if($this->data['Chidoansv']['loaidv']=='cb')
				{
					$cdoansvtk=array();
					$cdoansv =array('Chidoansv.id'=>array());
					
					$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
	    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
    						where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
    							group by Tgiachidoancb.chidoansv_id";
    					$laycdcb_act = $this->Chidoansv->query($querystring);
    	
    					$cdcb_id = array();
    					foreach ($laycdcb_act as $cd) 
				    	{
				    		$cdcb_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
				    	}    
						$this->Chidoansv->displayField='chidoan' ;
				    	$cdcbs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'id'=>$cdcb_id)));
					$cdoancbtk =$cdcbs;
					$cdoancb =array('Chidoansv.id'=>array_keys($cdcbs));
				}
				elseif($this->data['Chidoansv']['loaidv']=='sv')
				{
						//sinh vien
						$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
				    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
				    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
				    						group by Tgiachidoan.chidoansv_id";
				    	$laycdsv_act = $this->Chidoansv->query($querystring);
				    	
				    	$cdsv_id = array();
				    	foreach ($laycdsv_act as $cd) 
				    	{
				    		$cdsv_id[] = $cd['Tgiachidoan']['chidoansv_id'];
				    	}
				    	if(empty($this->data['Chidoansv']['khoahocid']))
				    		$kh_id = array_keys($khoahocs);
				    	else
				    		$kh_id = $this->data['Chidoansv']['khoahocid'];
						//hien thi sinhvien can cu vao $sv 
						$this->Chidoansv->displayField='chidoan' ;
				    	$cdsv = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>$kh_id,'id'=>$cdsv_id)));
						$cdoancbtk =array();
						$cdoancb =array('Chidoansv.id'=>array());
						$cdoansvtk = $cdsv;
						$cdoansv =array('Chidoansv.id'=>array_keys($cdsv));
				}
				else
				{
					$querystring= "select Tgiachidoan.chidoansv_id, sl as soluong
    					from tgiachidoans as Tgiachidoan, (select a.chidoansv_id, count(sinhvien_id) as sl from tgiachidoans a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoan1
    					where Tgiachidoan.chidoansv_id = Tgiachidoan1.chidoansv_id and Tgiachidoan1.sl>0 and ngaychuyen is NULL
			    						group by Tgiachidoan.chidoansv_id";
			    	$laycdsv_act = $this->Chidoansv->query($querystring);
			    	
			    	$cdsv_id = array();
			    	foreach ($laycdsv_act as $cd) 
			    	{
			    		$cdsv_id[] = $cd['Tgiachidoan']['chidoansv_id'];
			    	}    	
					//hien thi sinhvien can cu vao $sv 
					$this->Chidoansv->displayField='chidoan' ;
			    	$cdsvs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id !='=>null,'id'=>$cdsv_id)));
			    	
			    	/// can bo
			    	$querystring= "select Tgiachidoancb.chidoansv_id, sl as soluong
			    					from tgiachidoancbs as Tgiachidoancb, (select a.chidoansv_id, count(canbo_id) as sl from tgiachidoancbs a where ngaychuyen is NULL group by a.chidoansv_id) as Tgiachidoancb1
			    					where Tgiachidoancb.chidoansv_id = Tgiachidoancb1.chidoansv_id and Tgiachidoancb1.sl>0 and ngaychuyen is NULL
			    						group by Tgiachidoancb.chidoansv_id";
			    	$laycdcb_act = $this->Chidoansv->query($querystring);
			    	
			    	$cdcb_id = array();
			    	foreach ($laycdcb_act as $cd) 
			    	{
			    		$cdcb_id[] = $cd['Tgiachidoancb']['chidoansv_id'];
			    	}    
					$this->Chidoansv->displayField='chidoan' ;
			    	$cdcbs = $this->Chidoansv->find('list',array('conditions'=>array('khoa_id'=>$khoa_id,'khoahoc_id'=>null,'id'=>$cdcb_id)));
			    	
			    	$cdoancbtk =$cdcbs;
					$cdoancb =array('Chidoansv.id'=>array_keys($cdcbs));
					$cdoansvtk = $cdsvs;
					$cdoansv =array('Chidoansv.id'=>array_keys($cdsvs));
				}
			}
			//sinh vien
			$joins =array(array('table' => 'sinhviens',
            						'type' => 'INNER',
            						'alias' => 'Sinhvien',
            						'conditions' => array(
                					'Tgiachidoan.sinhvien_id = Sinhvien.id')),
									array('table' => 'chidoansvs',
            						'type' => 'INNER',
            						'alias' => 'Chidoansv',
            						'conditions' => array(
                					'Tgiachidoan.chidoansv_id = Chidoansv.id')));
			$thongkethanhniensv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'OR'=>array(array('Sinhvien.ngayvaodoan '=>null),
																																				array('Sinhvien.ngayvaodoan'=>'0000-00-00'))),
																												'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'),
																												'group'=>array('Chidoansv.id'))));
			$thongkethanhniennamnusv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'Sinhvien.phai'=>'Nữ',
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'OR'=>array(array('Sinhvien.ngayvaodoan '=>null),
																																				array('Sinhvien.ngayvaodoan'=>'0000-00-00'))),
																												'fields'=>array('Chidoansv.*','Sinhvien.phai','count(Sinhvien.id) as soluong'),
																												'group'=>array('Chidoansv.id','Sinhvien.phai'))));
			$thongkedoanviensv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'AND'=>array(array('Sinhvien.ngayvaodoan !='=>null),
																																				array('Sinhvien.ngayvaodoan !='=>'0000-00-00'))),
																												'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'),
																												'group'=>array('Chidoansv.id'))));
			$thongkedoanviennamnusv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'Sinhvien.phai'=>'Nữ',
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'AND'=>array(array('Sinhvien.ngayvaodoan !='=>null),
																																				array('Sinhvien.ngayvaodoan !='=>'0000-00-00'))),
																												'fields'=>array('Chidoansv.*','Sinhvien.phai','count(Sinhvien.phai) as soluong'),
																												'group'=>array('Chidoansv.id','Sinhvien.phai'))));
			$thongkedangviensv = $this->Tgiachidoan->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,
																										$cdoansv,$dtoc,$tgiao,$tinhtp,
																										'AND'=>array(array('Sinhvien.ngayvaodang !='=>null),
																													array('Sinhvien.ngayvaodang !='=>'0000-00-00'))),
																						'fields'=>array('Chidoansv.*','count(Sinhvien.id) as soluong'),
																						'group'=>array('Chidoansv.id')));
			$tsthanhniensv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'OR'=>array(array('Sinhvien.ngayvaodoan '=>null),
																																				array('Sinhvien.ngayvaodoan'=>'0000-00-00'))),
																												'fields'=>array('count(Sinhvien.id) as soluong'))));
			$tsdoanviensv = $this->Chidoansv->query($this->Tgiachidoan->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,
																																	$cdoansv,$dtoc,$tgiao,$tinhtp,
																																	'OR'=>array(array('Sinhvien.ngayvaodang'=>NULL),
																																				array('Sinhvien.ngayvaodang'=>'0000-00-00')),
																																	'AND'=>array(array('Sinhvien.ngayvaodoan !='=>null),
																																				array('Sinhvien.ngayvaodoan !='=>'0000-00-00'))),
																												'fields'=>array('count(Sinhvien.id) as soluong'))));
			//can bo
		$joins =array(array('table' => 'canbos',
            						'type' => 'INNER',
            						'alias' => 'Canbo',
            						'conditions' => array(
                					'Tgiachidoancb.canbo_id = Canbo.id')),
									array('table' => 'chidoansvs',
            						'type' => 'INNER',
            						'alias' => 'Chidoansv',
            						'conditions' => array(
                					'Tgiachidoancb.chidoansv_id = Chidoansv.id')));
		
		$thongkedoanviencb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'AND'=>array(array('Canbo.ngayvaodoan !='=>null),
																																					array('Canbo.ngayvaodoan !='=>'0000-00-00'))),
																													'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'),
																													'group'=>array('Chidoansv.id'))));
		$thongkedoanviennamnucb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'Canbo.phai'=>'Nữ',
																																		$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'AND'=>array(array('Canbo.ngayvaodoan !='=>null),
																																					array('Canbo.ngayvaodoan !='=>'0000-00-00'))),
																													'fields'=>array('Chidoansv.*','Canbo.phai','count(Canbo.phai) as soluong'),
																													'group'=>array('Chidoansv.id','Canbo.phai'))));
		$thongkethanhniencb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'OR'=>array(array('Canbo.ngayvaodoan '=>null),
																																					array('Canbo.ngayvaodoan'=>'0000-00-00'))),
																													'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'),
																													'group'=>array('Chidoansv.id'))));
		$thongkethanhniennamnucb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,'Canbo.phai'=>'Nữ',
																																		$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'OR'=>array(array('Canbo.ngayvaodoan '=>null),
																																					array('Canbo.ngayvaodoan'=>'0000-00-00'))),
																													'fields'=>array('Chidoansv.*','Canbo.phai','count(Canbo.phai) as soluong'),
																													'group'=>array('Chidoansv.id','Canbo.phai'))));
		$thongkedangviencb = $this->Tgiachidoancb->find('all',array('recursive'=>0,'conditions'=>array('ngaychuyen'=>null,
																										$cdoancb,$dtoc,$tgiao,$tinhtp,
																										'AND'=>array(array('Canbo.ngayvaodang !='=>null),
																														array('Canbo.ngayvaodang !='=>'0000-00-00'))),
																					'fields'=>array('Chidoansv.*','count(Canbo.id) as soluong'),
																					'group'=>array('Chidoansv.id')));
																														
		$tsthanhniencb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'OR'=>array(array('Canbo.ngayvaodoan '=>null),
																																					array('Canbo.ngayvaodoan'=>'0000-00-00'))),
																													'fields'=>array('count(Canbo.id) as soluong'))));
		$tsdoanviencb = $this->Chidoansv->query($this->Tgiachidoancb->generatequery(array('joins'=>$joins,'conditions'=>array('ngaychuyen'=>null,$cdoancb,$dtoc,$tgiao,$tinhtp,
																																		'OR'=>array(array('Canbo.ngayvaodang'=>NULL),
																																					array('Canbo.ngayvaodang'=>'0000-00-00')),
																																		'AND'=>array(array('Canbo.ngayvaodoan !='=>null),
																																					array('Canbo.ngayvaodoan !='=>'0000-00-00'))),
																													'fields'=>array('count(Canbo.id) as soluong'))));
		
																																					
		
		$thongke['thanhnien']=(isset($tsthanhniensv[0][0]['soluong'])?$tsthanhniensv[0][0]['soluong']:0)+(isset($tsthanhniencb[0][0]['soluong'])?$tsthanhniencb[0][0]['soluong']:0);
		$thongke['doanvien']=(isset($tsdoanviensv[0][0]['soluong'])?$tsdoanviensv[0][0]['soluong']:0)+(isset($tsdoanviencb[0][0]['soluong'])?$tsdoanviencb[0][0]['soluong']:0);
		
		$this->set(compact('thongke','namess'));
		$this->set(compact('cdoansvtk','thongkethanhniensv','thongkethanhniennamnusv','thongkedoanviensv','thongkedoanviennamnusv','thongkedangviensv'));		
		$this->set(compact('cdoancbtk','thongkethanhniencb','thongkethanhniennamnucb','thongkedoanviencb','thongkedoanviennamnucb','thongkedangviencb'));
		}
		$this->set(compact('tongiaos','dantocs','khoahocs','tinhtps','chidoans','tenkhoa'));	
	}
}

	function printTree($chidoans,$cha='',$html)
	{
		$row = count($chidoans);
		$count= 0;
		foreach ($chidoans as $chidoan)
		{
				$count =$count +1;
				if ($chidoan['id_cha']==$cha)
				{
					echo "<li>";
					echo $html->link($chidoan['tencd'], array('controller'=>'chidoansvs','action'=>'doanviencd',$chidoan['loaidv'],$chidoan['id']), array('class'=>'chidoan','id'=>$chidoan['id']));
					$id_cha = $chidoan['id'];
					echo "<ul>";
					printTree($chidoans, $id_cha,$html);
					echo "</ul>";
					echo "</li>";
				}
		}
	} 